<?
class DB
{
	var $conn,$table,$rows;
	function __construct($t='',$other_conn=null)
	{
		global $db_host,$db_port,$db_user,$db_password,$db_database;
		if(is_null($other_conn)) {
			$this->conn = new PDO('mysql:host='.$db_host.';port='.$db_port.';dbname='.$db_database.';charset=utf8', $db_user, $db_password);
			$this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			$this->conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
			$this->conn->exec("SET NAMES 'utf8';");
		}
		else {
			$this->conn=$other_conn;
		}
		if($t!='') $this->table=$t;
		$this->rows=15;
	}
	function get_conn(){
		return $this->conn;
	}
	function get_last_insert_id(){
		return $this->conn->lastInsertId();
	}
	function begin_transaction() { $this->conn->beginTransaction();}
	function commit() { $this->conn->commit();}
	function rollback() { $this->conn->rollBack();}

	// 用于查询 select
	function query($sql){ $result = $this->conn->query($sql); return $result;}
	// 用于增删改 insert/update/delete
	function exec($sql) { $result = $this->conn->exec($sql); return $result;}
	// 用于预定义 prepare
	function prepare($sql) { $result = $this->conn->prepare($sql); return $result;}
	// 将查询封装为数组 results to array
	function query_to_array($sql){
		$result = $this->conn->query($sql);
		$results = $result->fetchAll(PDO::FETCH_BOTH);
		return $results;
	}
	function fetch_array($result){$row = $result->fetch(PDO::FETCH_BOTH); return $row; }
	function fetch_row($result){$row = $result->fetch(PDO::FETCH_BOTH); return $row; }
	function fetch_object($result){$row = $result->fetch(PDO::FETCH_OBJ); return $row; }
	function num_row($result){$result = $num=$result->rowCount();return $num; }
	function num_rows($result){$result = count($result);return $result; }
	function check_unique($key,$val)
	{	
		$sql="SELECT * FROM $this->table WHERE " . $key . " = :val; ";
		$stmt = $this->prepare($sql);
		$stmt->execute(array(':val'=>$val));
		$result = $stmt->rowCount();
		return $result;
	}
	function check_update_unique($id,$key,$val)
	{
		$sql="SELECT * FROM $this->table WHERE id != :id AND " . $key . " = :val ";
		$stmt = $this->prepare($sql);
		$stmt->execute(array(':id'=>$id,':val'=>$val));
		$result = $stmt->rowCount();
		return $result;
	}
	function insert($data)
	{
		$field="";
		$value="";
		$parameters=array();
		foreach ($data as $key => $val)
		{
			$field.="$key,";
			$value.=":$key,";
			$parameters[":$key"]=$val;
		}
		//cut trailing commas
		$field=rtrim($field, ",");
		$value=rtrim($value, ",");

		$stmt = $this->prepare("INSERT INTO $this->table ($field) VALUES ($value)");
		$stmt->execute($parameters);

		if($stmt->rowCount()) return $stmt->rowCount();
		//return false;
		print_r($stmt->errorInfo());
	}
	function update($data, $where='id', $value='') // $value 可以为单个值或数组
	{
		$parameters=array();
		$field='';
		foreach ($data as $key => $val){
			$field.="$key=?,";
			$parameters[]=$val;
		}
		$field=rtrim($field, ",");
		if(is_array($value)){
			$str='0';
			foreach($value as $row) $str.=','.$row;
			$sql = "UPDATE $this->table SET ".$field." where $where in (".$str.") ";
		}
		else {
			$parameters[]=$value;
			$sql = "UPDATE $this->table SET ".$field." where $where = ? ";
		}
		$stmt = $this->prepare($sql);
		$stmt->execute($parameters);
		return $stmt->rowCount();
		//print_r($stmt->errorInfo());
	}
	function delete($id, $where='id')
	{
		$stmt = $this->prepare("DELETE FROM $this->table WHERE $where = :id");
		$stmt->execute(array(':id'=>$id));
		$result = $stmt->rowCount();
		return $result;
	}
	function delete_rows($arr) // array(1,3,5,9) 参数为数组
	{
		$str='(0';
		foreach($arr as $row) $str.=','.$row;
		$str.=')';
		$sql = "DELETE FROM $this->table where id in $str ";
		return $stmt = $this->exec($sql);
		// $stmt->execute();
		// $result = $stmt->rowCount();
		// return $result;
	}
	function get_row($val,$where='id')
	{
		$stmt = $this->prepare("SELECT * FROM $this->table where $where =:id limit 1 ");
		$stmt->execute(array(':id'=>$val));
		$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
		if(count($rows)) return $rows[0];
		else return null;
	}
	// 参数: 数量/字段/值/排序字段/排序方向
	function get_rows($n=10, $where='', $value='', $order_by='id', $order_direction='desc')
	{
		$condition='';
		$limit='';
		$parameters=array();
		if(!empty($where)){
			$condition=" WHERE $where=:$where ";
			$parameters[":$where"]=$value;
		}
		if($n>0) {
			$limit=" limit $n ";
		}
		$stmt = $this->prepare("SELECT * FROM $this->table $condition order by $order_by $order_direction $limit ;");
		$stmt->execute($parameters);
		$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
		return $rows;
	}
	// 参数: 数量/条件数组/排序字段/排序方向
	function get_rows_advanced($n=10, $where=array(), $order_by='id', $order_direction='desc', $onlyconn=0){
		$str_order_by = ' order by '.$order_by.' '.$order_direction.' ';
		if($order_by != 'id') $str_order_by .= ', id desc ';

		$arr=$this->where($where);
		$condition=$arr['condition'];
		$parameters=$arr['parameters'];
		$sql="SELECT * FROM $this->table $condition $str_order_by limit $n ";
		$stmt = $this->prepare($sql);
		$stmt->execute($parameters);
		if($onlyconn) return $stmt;
		$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
		return $rows;
	}
	// 参数: 页码/条件数组/排序字段/排序方向
	function get_page($page_num, $where=array(), $order_by='id', $order_direction='desc')
	{
		$str_order_by = ' order by '.$order_by.' '.$order_direction.' ';
		if($order_by != 'id') $str_order_by .= ', id desc ';

		$arr=$this->where($where);
		$condition=$arr['condition'];
		$parameters=$arr['parameters'];
		
		$start = ($page_num-1) * $this->rows;
		$sql="SELECT * FROM $this->table $condition $str_order_by LIMIT $start, $this->rows ";
		$stmt = $this->prepare($sql);
		$stmt->execute($parameters);
		$rows = $stmt->fetchAll(PDO::FETCH_BOTH);
		return $rows;
	}
	function count_rows($where=array()){
		$arr=$this->where($where);
		$condition=$arr['condition'];
		$parameters=$arr['parameters'];

		$sql="SELECT count(id) as num FROM $this->table $condition ";
		//echo $sql;
		$stmt = $this->prepare($sql);
		$stmt->execute($parameters);
		$rows = $stmt->fetchAll(PDO::FETCH_BOTH);
		return $rows[0][0];
	}
	function count_pages($where=array())
	{
		$total_pages = ceil($this->count_rows($where)/$this->rows);
		return $total_pages;
	}
	function where($where){
		$condition=' where id > 0 ';
		$parameters=array();
		if(isset($where)){
			foreach($where as $tem){
				//set default value if not set
				if(! isset($tem['opn'])) $tem['opn']='=';
				if(! isset($tem['and'])) $tem['and']='and';
				if(! isset($tem['start'])) $tem['start']='';
				if(! isset($tem['end'])) $tem['end']='';
				//trim operand
				$tem['opn']=trim($tem['opn']);
				if($tem['opn']=='in') {
					$temparray=$tem['val'];
					$txt='';
					if(! is_array($temparray)) $txt='('.$temparray.')';
					else {
						$txt.='(';
						foreach ($temparray as $row){
							$txt.='?,';
							$parameters[]=$row;
						}
						$txt.='0)';
					}
					$condition.=' '.$tem['and'].' '.$tem['start'].$tem['name'].' in '.$txt.' '.$tem['end'];
				}
				if($tem['opn']=='=') {
					$condition.=' '.$tem['and'].' '.$tem['start'].$tem['name'].' = ? '.$tem['end'];
					$parameters[]=$tem['val'];
				}
				if($tem['opn']=='<') {
					if(isset($tem['name2'])) {
						$condition.=' '.$tem['and'].' '.$tem['start'].$tem['name'].' < '.$tem['name2'].' '.$tem['end'];
					}
					else {
						$condition.=' '.$tem['and'].' '.$tem['start'].$tem['name'].' < ? '.$tem['end'];
						$parameters[]=$tem['val'];					
					}
				}
				if($tem['opn']=='>') {
					if(isset($tem['name2'])) {
						$condition.=' '.$tem['and'].' '.$tem['start'].$tem['name'].' > '.$tem['name2'].' '.$tem['end'];
					}
					else {
						$condition.=' '.$tem['and'].' '.$tem['start'].$tem['name'].' > ? '.$tem['end'];
						$parameters[]=$tem['val'];				
					}
				}
				if($tem['opn']=='like') {
					$condition.=' '.$tem['and'].' '.$tem['start'].$tem['name'].' like ? '.$tem['end'];
					$parameters[]='%'.$tem['val'].'%';
				}
				if($tem['opn']=='<>' or $tem['opn']=='!=') {
					$condition.=' '.$tem['and'].' '.$tem['start'].$tem['name'].' <> ? '.$tem['end'];
					$parameters[]=$tem['val'];
				}
				if($tem['opn']=='between') {
					$condition.=' '.$tem['and'].' '.$tem['start'].'( '.$tem['name'].' > ? and '.$tem['name'].' < ?) '.$tem['end'];
					$parameters[]=$tem['val'];
					$parameters[]=$tem['val2'];
				}
			}
		}
		$arr=array(
			'condition'=>$condition,
			'parameters'=>$parameters
		);
		return $arr;
	}
	// 参数: 页码/原生sql/排序字段/排序方向
	function get_page_raw($page_num, $where, $order_by='id', $order_direction='desc')
	{
		$str_order_by = ' order by '.$order_by.' '.$order_direction.' ';
		if($order_by == '') $str_order_by .= ', id desc ';
		
		$start = ($page_num-1) * $this->rows;
		$sql=$where." $str_order_by LIMIT $start, $this->rows ";
		$result=$this->query_to_array($sql);
		return $result;
	}
	function count_rows_raw($where){
		$result = $this->query($where);
		return num_row($result);
	}
	function count_pages_raw($where)
	{
		$total_pages = ceil($this->count_rows_raw($where)/$this->rows);
		return $total_pages;
	}
	function click($id)
	{
		return $this->exec("UPDATE $this->table set hit_count = hit_count+1 where id =".$id." limit 1 ");
	}
	function add($field='stock',$qty=0,$where='id',$value='')
	{
		return $this->exec("UPDATE $this->table set $field = $field + $qty where $where =".$value." limit 1 ");
	}
	function reduce($field='stock',$qty=0,$where='id',$value='')
	{
		return $this->exec("UPDATE $this->table set $field = $field - $qty where $where =".$value." limit 1 ");
	}
	// 空=cn, en/sc/ot非空
	function get_name($id, $lang=''){
		if(! empty($lang)) $lang='_'.$lang;
		$stmt = $this->prepare("SELECT name".$lang." FROM $this->table where id = :id limit 1 ");
		$stmt->execute(array(':id'=>$id));
		$result = $stmt->fetchAll(PDO::FETCH_BOTH);
		return $result[0][0];
	}
	function get_id($where='', $value='')
	{
		$wherer='';
		$parameters=array();
		if( ! empty($where) ) { $wherer=" where $where=:value "; $parameters[':value']=$value; }
		$sql="SELECT id FROM $this->table $wherer limit 1 ";
		$stmt = $this->prepare($sql);
		$stmt->execute($parameters);
		$result = $stmt->fetchAll(PDO::FETCH_BOTH);
		return $result[0][0];
	}
	function flip($id, $where='is_enabled')
	{
		$sql = "UPDATE $this->table SET $where=ABS($where-1) WHERE id = :id ";
		$stmt = $this->prepare($sql);
		$stmt->execute(array(':id'=>$id));
		$result = $stmt->rowCount();
		return $result;
	}

	function set_default($id, $where='is_default')
	{
		$sql = "UPDATE $this->table SET $where=0";
		$stmt = $this->prepare($sql);
		$stmt->execute();
		
		$sql = "UPDATE $this->table SET is_enabled=1, $where=1 WHERE id = :id ";
		$stmt = $this->prepare($sql);
		$stmt->execute(array(':id'=>$id));
		$result = $stmt->rowCount();
		return $result;
	}
	
	function get_last($where='', $value='')
	{
		$wherer='';
		$parameters=array();
		if( ! empty($where) ) { $wherer=" where $where=:value "; $parameters[':value']=$value; }
		$sql="SELECT * FROM $this->table $wherer ORDER BY id desc limit 1 ";
		$stmt = $this->prepare($sql);
		$stmt->execute($parameters);
		if($stmt->rowCount()==0) return 0;
		$result = $stmt->fetchAll(PDO::FETCH_BOTH);
		return $result[0];
	}
	function get_first($where='', $value='')
	{
		$wherer='';
		$parameters=array();
		if( ! empty($where) ) { $wherer=" where $where=:value "; $parameters[':value']=$value; }
		$sql="SELECT * FROM $this->table $wherer ORDER BY id asc limit 1 ";
		$stmt = $this->prepare($sql);
		$stmt->execute($parameters);
		if($stmt->rowCount()==0) return 0;
		$result = $stmt->fetchAll(PDO::FETCH_BOTH);
		return $result[0];
	}
	function get_left($id, $where='', $value='', $lang=0){
		$wherer='';
		$params=array();
		$params[':id']=$id;
		if( ! empty($where) ) { $wherer=" $where=:value and "; $params[':value']=$value; }
		if($lang!=0) $wherer.=" language_id=$lang and ";
		$sql="SELECT * FROM $this->table where $wherer id < :id order by id desc limit 1 ";
		$stmt = $this->prepare($sql);
		$stmt->execute($params);
		if($stmt->rowCount()==0) return 0;
		$result = $stmt->fetchAll(PDO::FETCH_BOTH);
		return $result[0];
	}
	function get_right($id, $where='', $value='', $lang=0){
		$wherer='';
		$params=array();
		$params[':id']=$id;
		if( ! empty($where) ) { $wherer=" $where=:value and "; $params[':value']=$value; }
		if($lang!=0) $wherer.=" language_id=$lang and ";
		$sql="SELECT * FROM $this->table where $wherer id > :id order by id asc limit 1 ";
		$stmt = $this->prepare($sql);
		$stmt->execute($params);
		if($stmt->rowCount()==0) return 0;
		$result = $stmt->fetchAll(PDO::FETCH_BOTH);
		return $result[0];
	}
}